Problem:
I’ve a database with server collation of utf8 / utf8_bin. DB have arabic utf8 text with accent chars (zabar / kasar etc). I want to get text without accent chars.
For e.g. SELECT replace(field1,0x[CODE],”) — where [CODE] is the accent char.
REPLACE() is working fine when i write static text in place of field1. But it does not replace when it run at database field.
Solution:
After reading an article, i come to know there are 2 (client & server) encodings used with mysql. My server encoding (table charset/collation) was utf8, but client’s encoding was latin1 and the time of INSERT.
So data stored in DB is somewhat mixed, due to which REPLACE() was not working as expected.
FIX:
- i fetched the data using php,
- applied bin2hex() function on data,
- set client’s encoding to utf8 (set names utf8) and
- updated same record.
This fixed my client’s encoding to utf8 that matches with server encoding, now REPLACE() is removing that accent hex code and working as expected .
Following article really helped …
http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html
Share your thoughts & feedback